package com.tbynet.jwp.repository.impl;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.tbynet.jwp.framework.aop.Aop;
import com.tbynet.jwp.framework.core._JwpServiceProvider;
import com.tbynet.jwp.model.Posts;
import com.tbynet.jwp.repository.spi.PostStatService;

import java.util.List;

@Aop(PostStatService.class)
public class PostStatServiceImpl extends _JwpServiceProvider<Posts> implements PostStatService {

    @Override
    protected String buildSearchWhereSql(String keyword, List<Object> params) {
        return "";
    }

    /**
     * 获取热门分类统计
     * 按文章数量排序返回前10个热门分类
     *
     * @return 热门分类列表，包含分类名称和文章数量
     */
    @Override
    public List<Record> getPopularCategories() {
        String sql = "SELECT t.name, tt.count " +
                "FROM wp_terms t " +
                "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                "WHERE tt.taxonomy = 'category' " +
                "ORDER BY tt.count DESC " +
                "LIMIT 10";
        return Db.find(sql);
    }

    /**
     * 获取文章趋势数据
     * 根据指定周期返回文章发布趋势
     *
     * @param period 时间周期：month-月度, week-周度, day-日度
     * @return 趋势数据列表
     */
    @Override
    public List<Record> getTrendData(String period) {
        switch (period) {
            case "week":
                return getWeeklyTrend();
            case "day":
                return getDailyTrend();
            case "month":
            default:
                return getMonthlyTrend();
        }
    }

    /**
     * 获取月度趋势数据
     * 返回过去12个月的文章发布统计
     *
     * @return 月度趋势数据
     */
    @Override
    public List<Record> getMonthlyTrend() {
        String sql = "SELECT DATE_FORMAT(post_date, '%Y-%m') as period, " +
                "SUM(CASE WHEN post_status = 'publish' THEN 1 ELSE 0 END) as publish_count, " +
                "SUM(CASE WHEN post_status = 'draft' THEN 1 ELSE 0 END) as draft_count " +
                "FROM wp_posts " +
                "WHERE post_type = 'post' AND post_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH) " +
                "GROUP BY DATE_FORMAT(post_date, '%Y-%m') " +
                "ORDER BY period";
        return Db.find(sql);
    }

    /**
     * 获取周度趋势数据
     * 返回过去12周的文章发布统计
     *
     * @return 周度趋势数据
     */
    @Override
    public List<Record> getWeeklyTrend() {
        String sql = "SELECT YEARWEEK(post_date) as period, " +
                "SUM(CASE WHEN post_status = 'publish' THEN 1 ELSE 0 END) as publish_count, " +
                "SUM(CASE WHEN post_status = 'draft' THEN 1 ELSE 0 END) as draft_count " +
                "FROM wp_posts " +
                "WHERE post_type = 'post' AND post_date >= DATE_SUB(NOW(), INTERVAL 12 WEEK) " +
                "GROUP BY YEARWEEK(post_date) " +
                "ORDER BY period";
        return Db.find(sql);
    }

    /**
     * 获取日度趋势数据
     * 返回过去30天的文章发布统计
     *
     * @return 日度趋势数据
     */
    @Override
    public List<Record> getDailyTrend() {
        String sql = "SELECT DATE(post_date) as period, " +
                "SUM(CASE WHEN post_status = 'publish' THEN 1 ELSE 0 END) as publish_count, " +
                "SUM(CASE WHEN post_status = 'draft' THEN 1 ELSE 0 END) as draft_count " +
                "FROM wp_posts " +
                "WHERE post_type = 'post' AND post_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) " +
                "GROUP BY DATE(post_date) " +
                "ORDER BY period";
        return Db.find(sql);
    }

    /**
     * 获取分类分布统计
     * 返回各分类的文章数量及占比
     *
     * @return 分类分布数据
     */
    @Override
    public List<Record> getCategoryDistribution() {
        String sql = "SELECT t.name, tt.count, " +
                "ROUND((tt.count * 100.0 / (SELECT COUNT(*) FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish')), 2) as percentage " +
                "FROM wp_terms t " +
                "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                "WHERE tt.taxonomy = 'category' AND tt.count > 0 " +
                "ORDER BY tt.count DESC";
        return Db.find(sql);
    }

    /**
     * 获取标签统计
     * 返回前20个热门标签及其文章数量
     *
     * @return 标签统计数据
     */
    @Override
    public List<Record> getTagStats() {
        String sql = "SELECT t.name, tt.count " +
                "FROM wp_terms t " +
                "INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id " +
                "WHERE tt.taxonomy = 'post_tag' " +
                "ORDER BY tt.count DESC " +
                "LIMIT 20";
        return Db.find(sql);
    }

    /**
     * 获取作者活动统计
     * 统计各作者的文章数量、最后发文时间等信息
     *
     * @return 作者活动统计数据
     */
    @Override
    public List<Record> getAuthorActivityStats() {
        String sql = "SELECT u.display_name, " +
                "COUNT(p.ID) as post_count, " +
                "MAX(p.post_date) as last_post_date " +
                "FROM wp_users u " +
                "LEFT JOIN wp_posts p ON u.ID = p.post_author AND p.post_type = 'post' AND p.post_status = 'publish' " +
                "GROUP BY u.ID, u.display_name " +
                "ORDER BY post_count DESC";
        return Db.find(sql);
    }

    /**
     * 获取文章状态统计
     * 统计各状态的文章数量
     *
     * @return 文章状态统计数据
     */
    public List<Record> getPostStatusStats() {
        String sql = "SELECT post_status, COUNT(*) as count " +
                "FROM wp_posts " +
                "WHERE post_type = 'post' " +
                "GROUP BY post_status " +
                "ORDER BY count DESC";
        return Db.find(sql);
    }
}
